App Builder - Database based Plugin builders for Web components.

One of the versions I tried a while back was a completely web based version of the builder, I eventually gave up on that as the whole concept of writing to files on the server, and revision control was just too complicated and not that well suited to our general workflow (develop locally on the filesystem etc.)

 
The old browser based builder (not really maintained)

However one bit that was quite handy on the web based version, was that we could construct forms and grids by parsing the database schema's and very rapidly create an interactive form for any piece of data. This was possible as the web version had direct access to our whole DataObjects driven schema tools. So it had a deep understanding of the 'assumed' relationships. These relationships (links.ini) files from DB_DataObject are normally hand crafted, as Mysql does not have a very good concept of loose foreign keys. In postgresql foreign keys can be set up so that they are either NULL or point to the foreign table. In Mysql this is not feasible, and we also tend to store '0' as the value when it does not need to point to a foreign table.

The trick of using '0' is mostly due to the HTML/PHP<->Mysql mapping of base types, NULL is not really a first class type when dealing wht HTTP and PHP, it's difficult to send 'null' from a web browser to PHP and then end up in Mysql - It's basically for more reliable to use '0' as an indicator of nothing being selected.

So many moons ago when the original DB_DataObject design was done, apart from Mysql lacking even basic Foreign key support, we set up the links.ini to store the relationships between tables, in the same way that foriegn keys are done normally. As an asside, the generator can actually read Postgress Foreign keys and generate these links.ini files.  They are also used to generate automatic Joins etc.. 

Very early on in the builder's life, it became clear that we needed to copy elements from on file to another (eg. a combo box, or a submit button etc..) These elements may consist of multiple object, and only need minor adjustments when used in other locations. In the builder, you would right click on the tree element and select 'save template', fill in a name, then it would be written to the local PC's 'builder' directory under ~/.Builder/{Element type}/{the name you chose}.json

When adding an element to the builder tree, if there was elements in the template directory, before a new node was added, you would be prompted asking you if you wanted to use a template. Saving quite a bit of work adding standard elements in the UI.

This was idea later improved by allowing you to drag elements from one builder instance to another (eg. dragging whole UI elements  between files). You could basically take a whole 'themed' page and create a new website, by just dragging the root element of the tree.

 

To enable the builder to quickly create elements based on the database schema, I original wrote a seed script that queried the database, and looked at the links.ini files, and generated templates for all the relivant elements. The downside to this was that once you had created template elements for multiple databases, the list of templates became extremely long and undweildy. Hence I finally got round to addressing this. 

To enable the desktop App Builder to access this relationship data, I decided, rather than rely on configuring it to access all the files in some way, it would be better to append this information to the COMMENT area of the Mysql Table (It's almost impossible to update the COMMENT area for rows in Mysql). That way the builder could query the database schema using libgda, and extract the relations ships by looking at the comment data.

The code that was written to do this is inside an addon to our Database Updateing code https://github.com/roojs/Pman.Core/blob/master/UpdateDatabase/MysqlLinks.php.  We keep all our database schema details in .sql files inside a folder in our modules. We normally have various folder, 'sql' for generic schema (table definitions and changes to them), mysql/pgsql for stored procedures specific to a database type. The UpdateDatabase code is run at the command line and just applies any of these updates to the database. The new code we added other that adding comments to the database, also optionally added triggers to all the tables, so that we could effectively do the same kind of verification that adding a foriegn key does. This adds an extra level of verification so that mistakes done in the PHP code in deleting or modifying data, are checked against the database. It prevents you deleting related data, or pointing values a data that does not exist.

The Database integration is in two parts in the builder, the first is establishing the connection, this is done by an extra tab on the project properties, It's a very simple form, that you can fill in the database name, username/password, and database type (MySQL is the only one that really works at present). Then just press test connection

 

The when you add an element to the tree, along with  being prompted to add a element from a templates, it also offers you a list of the database tables.  If you pick a table, and a file exists in the resources folder (see other post about builder resoures). Then it will show a dialog with a webkit window that runs the UI for the Plugin. We have two working plugins at present, one for a grid, and one for a form.

Both of these plugins then list all the rows in the database, (and referenced rows) . Then offer you various alteratives about how you want build the form or grid (eg. form entry type or grid with etc.)

As we go forward, the plan is to develop a few more of these plugins so that you can use them with the Bootstrap library - creating bootstrap forms or tables from the data in the database.